package com.web.dao;

import com.web.bean.PageModel;
import com.web.bean.选课及成绩;
import com.jdbc.DataSourceTool;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class 选课及成绩Dao {
    //增
    public boolean insertAchievement(选课及成绩 acvt) throws SQLException {
        Connection connection = DataSourceTool.getConnection();
        String sql = "insert into 选课及成绩(学号,课程代号,成绩,修课时间,备注)" +
                "values (?,?,?,?,?)";
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1,acvt.get学号());
            ps.setString(2,acvt.get课程代号());
            ps.setFloat(3, acvt.get成绩());
            ps.setString(4,acvt.get修课时间());
            ps.setString(5, acvt.get备注());
            int resul = ps.executeUpdate();
            if(resul>0){
                return true;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DataSourceTool.closeConnection(connection);
        }
        return false;
    }

    public boolean deleteAchievement(String 学号) throws SQLException {
        Connection connection = DataSourceTool.getConnection();
        String sql = "delete from 选课及成绩 where 学号 = ?";
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1,学号);
            int resul = ps.executeUpdate();
            //
            if(resul>0){
                return true;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DataSourceTool.closeConnection(connection);
        }
        return false;
    }

    //改
    public boolean updateAchievement(选课及成绩 acvt) throws SQLException {
        Connection connection = DataSourceTool.getConnection();
        String sql = "update 选课及成绩 set 课程代号=?,成绩=?,修课时间=?,备注=? where 学号=?; ";
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1,acvt.get课程代号());
            ps.setFloat(2, acvt.get成绩());
            ps.setString(3,acvt.get修课时间());
            ps.setString(4, acvt.get备注());
            ps.setString(5,acvt.get学号());
            int resul = ps.executeUpdate();
            if(resul>0){
                return true;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DataSourceTool.closeConnection(connection);
        }
        return false;
    }

    public List<选课及成绩> queryAchievement(String search , PageModel pageModel) throws SQLException {
        List<选课及成绩> list = new ArrayList<>();
        Connection connection = DataSourceTool.getConnection();
        String sql = "select * from 选课及成绩 where 学号 like ? limit ?,?;";
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1, "%"+search+"%");
            ps.setInt(2, pageModel.getStartIndex());
            ps.setInt(3, pageModel.getPageSize());
            ResultSet rs = ps.executeQuery();

            while(rs.next()){
                选课及成绩 acvt=new 选课及成绩();
                acvt.set学号(rs.getString(1));
                acvt.set课程代号(rs.getString(2));
                acvt.set成绩(rs.getFloat(3));
                acvt.set修课时间(rs.getString(4));
                acvt.set备注(rs.getString(5));
                list.add(acvt);

            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            //关闭链接
            DataSourceTool.closeConnection(connection);
        }
        return list;
    }
    public int queryAchievementCount(String search) {
        //--获取数据库链接
        Connection connection = DataSourceTool.getConnection();
        // -- 定义sql语句  占位符表示一个动态数据的入参
        String sql = "select count(*) from 选课及成绩 where 学号 like ?;";
        try {
            //--通过链接对象获取sql语句对象
            PreparedStatement ps = connection.prepareStatement(sql);//提供sql语句
            //
            ps.setString(1, "%"+search+"%");
            //--通过sql语句对象执行sql操作
            ResultSet result = ps.executeQuery();
            //遍历结果集
            if (result.next()) {//判断是否有下一条记录
                //返回结果
                return result.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //--关闭链接
            DataSourceTool.closeConnection(connection);
        }
        // 返回0
        return 0;
    }
}
